
/* 

Name : upload_data.do 
Authors: Martin, Méjean, Parenti
Last update: Sept. 15, 2023 
Aim : 	This program 
		insheets the raw transaction data 
		cleans the dataset
		harmonize product codes over time
		output a lot of summary statistics on the dimensionality of the data

Input: panel_f2f_clean_19932017 saved in 
       + corres_nc8$firstyear$lastyear.dta (correspondance table of nc8 over time)
		
Output: 
	   transaction_all$firstyear$lastyear.dta and transaction_all_id_conc$firstyear$lastyear
	   ./corres/corres_idconc_nc8$firstyear$lastyear correspondance table between id_conc and nc8 

*/


*** 0. Create an excel file for saving summary statistics
clear
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Summary") replace
#delimit ; 
putexcel A1=("May, 2022 - run by Julien") 
A2=("Database presentation") 
A3=("Period $firtsyear-$lastyear") 
A4=("Country Sample: EU25")
A5=("From 2004: CY CZ EE HU LT LV MT PL SI SK")
A6=("From 2007: BG RO")
A7=("From 2013: HR")
 ; 
#delimit cr 


*** 1. Insheet data
cd $datapath 
use ./firm/panel_f2f_clean_19932017,clear
keep if year>=$firstyear & year<=$lastyear
rename numtvaanonym importer
cap rename mois month
rename kgs masse
rename unites usup
drop buyer
save ./firm/x_all$firstyear$lastyear, replace 


*** 2. remaining cleaning steps
* not already done in upload_data.do


*** 2.2.4 Drop trade flows involving a third party (iso2!=iso2_tva)
g iso2_tva=substr(importer,1,2)
replace iso2_tva="GR" if iso2_tva=="EL"
g dif=iso2!=iso2_tva 
tab dif 
*rename value export
egen tot=sum(export) 
egen tot_=sum(export), by(dif) 
quietly sum tot_ if dif==0
local tot_=r(mean)
local count_=r(N)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Coverage") modify
#delimit ; 
putexcel H1=("No 3rd party") 
 H2=("Count") I2=("Value")
 H3=(`count_') I3=(`tot_')
; 
#delimit cr 
drop tot tot_
egen tot=sum(export), by(year)
egen tot_=sum(export),by(year dif) 
local j=1
forvalues year=$firstyear(1)$lastyear{
	local k=3+`j'
	quietly sum tot_ if year==`year' & dif==0
	local tot_=r(mean)
	local count_=r(N)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Coverage") modify
	putexcel H`k'=(`count_') I`k'=(`tot_')
	local j=`j'+1
}
drop tot tot_ 
drop if iso2!=iso2_tva
drop dif

save ./firm/transaction_all$firstyear$lastyear, replace

* 2.3.2 nc8 concordance over time

putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Concordance") modify
#delimit ; 
putexcel A2=("$firstyear-$lastyear") 
 B1=("Count nc8") C1=("Count id_conc") D1=("Value")
E1=("Count nc8 unaffected") F1=("Value unaffected") G1=("Max count nc8 within id_conc")
H1=("Count id_conc multi-usup") I1=("Value id_conc multi-usup")
 ; 
#delimit cr 

* Input concordance (output of corres_nc8.do)
use ./corres/corres_nc8$firstyear$lastyear, clear
tostring nc8, replace
replace nc8="0"+nc8 if length(nc8)==7
sort nc8 year
save temp, replace
forvalues y=$firstyear(1)$lastyear{
	use temp, clear
	keep if year==`y'
	rename nc8 nc8_`y'
	sort id_conc
	save ./temp/corres_idconc_nc8`y', replace
}
* Input usup table (usup per nc8*year) which associates a product with an usup (default = 00 = no usup)
clear
insheet using ./corres/correspondance_nc8_usup.csv, delimiter(";") names
rename ann_nomenc year
tostring cod_nc8, g(nc8)
replace nc8="0"+nc8 if length(nc8)==7
drop if length(nc8)==3
keep year nc8 cod_usup
duplicates drop year nc8 cod_usup, force
duplicates tag year nc8, g(_)
keep if _==0|cod_usup!=.
drop _
sort year nc8
save temp2, replace
* merge concordance with transaction dataset and output summary statistics on concorded products
use ./firm/transaction_all$firstyear$lastyear, clear
egen _=nvals(nc8)
sum _
local max=r(max)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Concordance") modify
putexcel B2=(`max')
drop _
egen _=sum(export)
sum _
local max=r(max)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Concordance") modify
putexcel D2=(`max')
drop _
local j=1
forvalues year=$firstyear(1)$lastyear{
	local k=2+`j'
	egen _=nvals(nc8) if year==`year'
	sum _
	local max=r(max)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Concordance") modify
	putexcel A`k'=("`year'") B`k'=(`max')
	drop _
	egen _=sum(export) if year==`year'
	sum _
	local max=r(max)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Concordance") modify
	putexcel D`k'=(`max')
	drop _
	local j=`j'+1
}
sort nc8 year
merge m:m nc8 year using temp
tab nc8 if _merge==1
keep if _merge==3
drop _merge
egen _=nvals(id_conc)
sum _
local max=r(max)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Concordance") modify
putexcel C2=(`max')
drop _
local j=1
forvalues year=$firstyear(1)$lastyear{
	local k=2+`j'
	egen _=nvals(id_conc) if year==`year'
	sum _
	local max=r(max)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Concordance") modify
	putexcel C`k'=(`max')
	drop _
	local j=`j'+1
}
bys id_conc: egen count_nc=nvals(nc8)
bys id_conc year: egen count_nc_pery=nvals(nc8)
sum count_nc
local max=r(max)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Concordance") modify
putexcel G2=(`max')
local j=1
forvalues year=$firstyear(1)$lastyear{
	local k=2+`j'
	sum count_nc_pery if year==`year'
	local max=r(max)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Concordance") modify
	putexcel G`k'=(`max')
	local j=`j'+1
}
g __=(count_nc==1)
bys id_conc: g ___=__ if _n==1
egen count_unaff=sum(___)
sum count_unaff
local max=r(max)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Concordance") modify
putexcel E2=(`max')
egen value_unaff=sum(export) if __==1
sum value_unaff
local max=r(max)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Concordance") modify
putexcel F2=(`max')
drop _* count_unaff value_unaff
g __=(count_nc_pery==1)
bys id_conc year: g ___=__ if _n==1
local j=1
forvalues year=$firstyear(1)$lastyear{
	local k=2+`j'
	egen count_unaff=sum(___)  if year==`year'
	sum count_unaff
	local max=r(max)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Concordance") modify
	putexcel E`k'=(`max')
	drop count_unaff
	egen value_unaff=sum(export) if __==1 & year==`year'
	sum value_unaff
	local max=r(max)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Concordance") modify
	putexcel F`k'=(`max')
	drop value_unaff
	local j=`j'+1
}
* merge transaction with usup and statistics on concorded products with multiple usup
* (cannot aggregate usup whenever the definition of the usup is not consistent over concorded products)
sort year nc8
merge m:1 year nc8 using temp2
drop if _m==2
drop _merge
tostring cod_usup, replace
replace cod_usup="00" if cod_usup=="."
replace cod_usup="0"+cod_usup if length(cod_usup)==1
bys id_conc: egen count_usup=nvals(cod_usup)
bys id_conc year: egen count_usup_pery=nvals(cod_usup)
egen count_multiusup=nvals(id_conc) if count_usup>1
bys year: egen count_multiusup_pery=nvals(id_conc) if count_usup_pery>1
egen value_multiusup=sum(export) if count_usup>1
bys year: egen value_multiusup_pery=sum(export) if count_usup_pery>1
sum count_multiusup
local max=r(max)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Concordance") modify
putexcel H2=(`max')
sum value_multiusup
local max=r(max)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Concordance") modify
putexcel I2=(`max')
local j=1
forvalues year=$firstyear(1)$lastyear{
	local k=2+`j'
	sum count_multiusup_pery  if year==`year'
	local max=r(max)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Concordance") modify
	putexcel H`k'=(`max')
	sum value_multiusup_pery if year==`year'
	local max=r(max)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Concordance") modify
	putexcel I`k'=(`max')
	local j=`j'+1
}
tab indicmasse year
* From 2006 on, masse is no longer mandatory whenever usup is provided (indice_masse_nulle==0)

* collapse nc8 within an id_conc, siren, buyer, date
* Note that :
* Export can always be added. 
* Masse can be added although comparability of kilos might be questionable
* Usup can be added if it is the same units
destring cod_usup, g(cod_usup_n)
sort siren importer iso2 year month id_conc 
collapse (sum) export masse usup (max) count_nc_pery count_usup_pery cod_usup_n, by(siren importer iso2 year month id_conc  )
sum count_nc_pery, d
sum count_usup_pery, d
replace usup=. if count_usup_pery!=1
replace cod_usup_n=. if count_usup_pery!=1
* When several usup are used, one cannot sum across products within an id_conc
tostring cod_usup_n, g(cod_usup)
replace cod_usup="00" if cod_usup=="."
replace cod_usup="0"+cod_usup if length(cod_usup)==1
count if cod_usup=="00"
count if cod_usup=="00" & masse==0
count if cod_usup=="00" & masse==0 & usup==0
* when cod_usup="00" the quantity is in kilos
* do we want to compare kilos across nc8 within an id_conc?
g unitv=export/masse if cod_usup=="00"
count if cod_usup!="00"
count if cod_usup!="00" & usup==0
count if cod_usup!="00" & masse==0 & usup==0
* When cod_usup!="00" the quantity is usually in usup. It sometimes happens however that usup==0 & masse!=0
* What should we do in that case?
replace unitv=export/usup if cod_usup!="00"
save ./firm/transaction_all_id_conc$firstyear$lastyear, replace


/* 
* check that usup is comparable over time within an id_conc
bys id_conc: egen _=nvals(cod_usup)
sum _ if count_usup_pery==1, d
* Warning: it can happen that usup is changing over time in which case usup is not longer comparable over time within an id_conc

*** 3. Summary statistics on the dimensionality of the data
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Dimensionality") modify
#delimit ; 
putexcel A2=("All countries") B1=("Count siren") C1=("Count iso2 buyer") D1=("Count siren product") E1=("Count iso2 buyer product") F1=("Count siren iso2 buyer product") G1=("Count transactions")
 ; 
#delimit cr 
use ./firm/transaction_all_id_conc$firstyear$lastyear, clear
rename importer buyer 
g sample=0
foreach c in $countrysample{
		replace sample=1 if iso2=="`c'"
	}
	
do $dopath\id_group.do
id_group id_s "siren"
id_group id_b "iso2 buyer"
id_group id_sp "siren id_conc"
id_group id_bp "iso2 buyer id_conc"
id_group id_sbp "siren iso2 buyer id_conc"
sum id_s
local max=r(max)
local count=r(N)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Dimensionality") modify
putexcel B2=(`max') G2=(`count')
sum id_b
local max=r(max)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Dimensionality") modify
putexcel C2=(`max')
sum id_sp
local max=r(max)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Dimensionality") modify
putexcel D2=(`max')
sum id_bp
local max=r(max)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Dimensionality") modify
putexcel E2=(`max')
sum id_sbp
local max=r(max)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Dimensionality") modify
putexcel F2=(`max')
tab iso2

bys iso2: egen count_s=nvals(siren)
bys iso2: egen count_t=count(count_s)
bys iso2: egen count_b=nvals(iso2 buyer)
bys iso2: egen count_sp=nvals(siren id_conc)
bys iso2: egen count_bp=nvals(iso2 buyer id_conc)
bys iso2: egen count_sbp=nvals(siren iso2 buyer id_conc)
local j=1
foreach i in $countrysample{
	local k=2+`j'
	sum count_s if iso2=="`i'"
	local max=r(max)
	local count=r(N)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Dimensionality") modify
	putexcel A`k'=("`i'") B`k'=(`max') G`k'=(`count')
	sum count_b if iso2=="`i'"
	local max=r(max)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Dimensionality") modify
	putexcel C`k'=(`max')
	sum count_sp if iso2=="`i'"
	local max=r(max)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Dimensionality") modify
	putexcel D`k'=(`max')
	sum count_bp if iso2=="`i'"
	local max=r(max)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Dimensionality") modify
	putexcel E`k'=(`max')
	sum count_sbp if iso2=="`i'"
	local max=r(max)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Dimensionality") modify
	putexcel F`k'=(`max')
	local j=`j'+1
}


*** Statistics on the number of transactions per buyer*product
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Trans bp") modify
#delimit ; 
putexcel A2=("All countries") 
 B1=("Count One") C1=("Count Multiple") D1=("More than 2")
 E1=("More than 5") F1=("More than 10") G1=("More than 20")
 ; 
#delimit cr 
use ./firm/transaction_all_id_conc$firstyear$lastyear, clear
id_group id_bp "iso2 importer id_conc"
collapse (sum) export (count) count_transaction=export, by(id_bp iso2)
g Single=(count_transaction==1)
g Multiple=(count_transaction>1)
g More2=(count_transaction>2)
g More5=(count_transaction>5)
g More10=(count_transaction>10)
g More20=(count_transaction>20)
foreach i in Single Multiple More2 More5 More10 More20{
	g Value`i'=export*`i'
}
egen _=sum(Single)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Trans bp") modify
putexcel A2=("All countries") B2=(`sum')
drop _
egen _=sum(ValueSingle)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Trans bp") modify
putexcel I1=("Value One") I2=(`sum')
drop _
egen _=sum(Multiple)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Trans bp") modify
putexcel C2=(`sum')
drop _
egen _=sum(ValueMultiple)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Trans bp") modify
putexcel J1=("Value Multiple") J2=(`sum')
drop _
egen _=sum(More2)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Trans bp") modify
putexcel D2=(`sum')
drop _
egen _=sum(ValueMore2)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Trans bp") modify
putexcel K1=("Value More2") K2=(`sum')
drop _
egen _=sum(More5)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Trans bp") modify
putexcel E2=(`sum')
drop _
egen _=sum(ValueMore5)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Trans bp") modify
putexcel L1=("Value More5") L2=(`sum')
drop _
egen _=sum(More10)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Trans bp") modify
putexcel F2=(`sum')
drop _
egen _=sum(ValueMore10)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Trans bp") modify
putexcel M1=("Value More10") M2=(`sum')
drop _
egen _=sum(More20)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Trans bp") modify
putexcel G2=(`sum')
drop _
egen _=sum(ValueMore20)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Trans bp") modify
putexcel N1=("Value More20") N2=(`sum')
drop _

local j=1
foreach i in $countrysample{
	local k=2+`j'
	egen _=sum(Single)  if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Trans bp") modify
	putexcel A`k'=("`i'") B`k'=(`sum')
	drop _
	egen _=sum(ValueSingle)  if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Trans bp") modify
	putexcel I`k'=(`sum')
	drop _
	egen _=sum(Multiple)  if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Trans bp") modify
	putexcel C`k'=(`sum')
	drop _
	egen _=sum(ValueMultiple)  if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Trans bp") modify
	putexcel J`k'=(`sum')
	drop _
	egen _=sum(More2) if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Trans bp") modify
	putexcel D`k'=(`sum')
	drop _
	egen _=sum(ValueMore2) if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Trans bp") modify
	putexcel K`k'=(`sum')
	drop _
	egen _=sum(More5) if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Trans bp") modify
	putexcel E`k'=(`sum')
	drop _
	egen _=sum(ValueMore5) if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Trans bp") modify
	putexcel L`k'=(`sum')
	drop _
	egen _=sum(More10) if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Trans bp") modify
	putexcel F`k'=(`sum')
	drop _
	egen _=sum(ValueMore10) if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Trans bp") modify
	putexcel M`k'=(`sum')
	drop _
	egen _=sum(More20) if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Trans bp") modify
	putexcel G`k'=(`sum')
	drop _
	egen _=sum(ValueMore20) if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Trans bp") modify
	putexcel N`k'=(`sum')
	drop _
	local j=`j'+1
}

use ./firm/transaction_all_id_conc$firstyear$lastyear, clear
id_group id_bp "iso2 buyer id_conc"
bys id_bp iso2: egen count_transaction=count(export)
keep if count_transaction>50
collapse (mean) count_transaction (count) count_transaction_perseller=export, by(id_bp iso2 siren)
g share=count_transaction_perseller/count_transaction
collapse (count) count_sellers=share (mean) count_transaction (max) share, by(id_bp iso2)
count
count if share==1
count if share>.5
count if count_sellers>10


*** Statistics on the number of partners per buyer*product
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners bp") modify
#delimit ; 
putexcel A2=("All countries") 
 B1=("Count One") C1=("Count Multiple") D1=("More than 2")
 E1=("More than 5") F1=("More than 10") G1=("More than 20")
 I1=("Count One") J1=("Count Multiple") K1=("More than 2")
 L1=("More than 5") M1=("More than 10") N1=("More than 20")
 A36=("Note: Excludes buyers with one transaction over the period")
 ; 
#delimit cr 
use ./firm/transaction_all_id_conc$firstyear$lastyear, clear
id_group id_bp "iso2 importer id_conc"
bys id_bp: egen count=count(export)
drop if count==1
collapse (mean) count (sum) export, by(iso2 importer id_conc siren)
id_group id_bp "iso2 importer id_conc"
collapse (sum) export (mean) count (count) count_seller=export, by(id_bp iso2)

g Single=(count_seller==1)
g Multiple=(count_seller>1)
g More2=(count_seller>2)
g More5=(count_seller>5)
g More10=(count_seller>10)
g More20=(count_seller>20)
foreach i in Single Multiple More2 More5 More10 More20{
	g Value`i'=export*`i'
}
egen _=sum(Single)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners bp") modify
putexcel A2=("All countries") B2=(`sum')
drop _
egen _=sum(ValueSingle)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners bp") modify
putexcel I1=("Value One") I2=(`sum')
drop _
egen _=sum(Multiple)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners bp") modify
putexcel C2=(`sum')
drop _
egen _=sum(ValueMultiple)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners bp") modify
putexcel J1=("Value Multiple") J2=(`sum')
drop _
egen _=sum(More2)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners bp") modify
putexcel D2=(`sum')
drop _
egen _=sum(ValueMore2)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners bp") modify
putexcel K1=("Value More2") K2=(`sum')
drop _
egen _=sum(More5)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners bp") modify
putexcel E2=(`sum')
drop _
egen _=sum(ValueMore5)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners bp") modify
putexcel L1=("Value More5") L2=(`sum')
drop _
egen _=sum(More10)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners bp") modify
putexcel F2=(`sum')
drop _
egen _=sum(ValueMore10)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners bp") modify
putexcel M1=("Value More10") M2=(`sum')
drop _
egen _=sum(More20)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners bp") modify
putexcel G2=(`sum')
drop _
egen _=sum(ValueMore20)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners bp") modify
putexcel N1=("Value More20") N2=(`sum')
drop _

local j=1
foreach i in $countrysample{
	local k=2+`j'
	egen _=sum(Single)  if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners bp") modify
	putexcel A`k'=("`i'") B`k'=(`sum')
	drop _
	egen _=sum(ValueSingle)  if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners bp") modify
	putexcel I`k'=(`sum')
	drop _
	egen _=sum(Multiple)  if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners bp") modify
	putexcel C`k'=(`sum')
	drop _
	egen _=sum(ValueMultiple)  if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners bp") modify
	putexcel J`k'=(`sum')
	drop _
	egen _=sum(More2) if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners bp") modify
	putexcel D`k'=(`sum')
	drop _
	egen _=sum(ValueMore2) if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners bp") modify
	putexcel K`k'=(`sum')
	drop _
	egen _=sum(More5) if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners bp") modify
	putexcel E`k'=(`sum')
	drop _
	egen _=sum(ValueMore5) if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners bp") modify
	putexcel L`k'=(`sum')
	drop _
	egen _=sum(More10) if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners bp") modify
	putexcel F`k'=(`sum')
	drop _
	egen _=sum(ValueMore10) if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners bp") modify
	putexcel M`k'=(`sum')
	drop _
	egen _=sum(More20) if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners bp") modify
	putexcel G`k'=(`sum')
	drop _
	egen _=sum(ValueMore20) if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners bp") modify
	putexcel N`k'=(`sum')
	drop _
	local j=`j'+1
}

*** Statistics on the number of partners per buyer*product*date
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners bp*date") modify
#delimit ; 
putexcel A2=("All countries") 
 B1=("Count One") C1=("Count Multiple")
 E1=("Value One") F1=("Value Multiple")
 A36=("Note: Excludes buyers with one transaction over the period")
 ; 
#delimit cr 
use ./firm/transaction_all_id_conc$firstyear$lastyear, clear
id_group id_bp "iso2 importer id_conc"
bys id_bp: egen count=count(export)
drop if count==1
collapse (mean) count (sum) export, by(iso2 importer id_conc siren month year)
id_group id_bp "iso2 importer id_conc"
collapse (sum) export (mean) count (count) count_seller=export, by(id_bp iso2 month year)
g Single=(count_seller==1)
g Multiple=(count_seller>1)
foreach i in Single Multiple{
	g Value`i'=export*`i'
}
egen _=sum(Single)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners bp*date") modify
putexcel A2=("All countries") B2=(`sum')
drop _
egen _=sum(ValueSingle)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners bp*date") modify
putexcel E1=("Value One") E2=(`sum')
drop _
egen _=sum(Multiple)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners bp*date") modify
putexcel C2=(`sum')
drop _
egen _=sum(ValueMultiple)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners bp*date") modify
putexcel F1=("Value Multiple") F2=(`sum')
drop _

local j=1
foreach i in $countrysample{
	local k=2+`j'
	egen _=sum(Single)  if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners bp*date") modify
	putexcel A`k'=("`i'") B`k'=(`sum')
	drop _
	egen _=sum(ValueSingle)  if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners bp*date") modify
	putexcel E`k'=(`sum')
	drop _
	egen _=sum(Multiple)  if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners bp*date") modify
	putexcel C`k'=(`sum')
	drop _
	egen _=sum(ValueMultiple)  if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners bp*date") modify
	putexcel F`k'=(`sum')
	drop _
	local j=`j'+1
}


*** Statistics on the number of partners per siren*product*date*iso2
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp*date") modify
#delimit ; 
putexcel A2=("All countries") 
B1=("Count One") C1=("Count Multiple") D1=("More than 2")
 E1=("More than 5") F1=("More than 10") G1=("More than 20")
 I1=("Count One") J1=("Count Multiple") K1=("More than 2")
 L1=("More than 5") M1=("More than 10") N1=("More than 20")
 A36=("Note: Excludes buyers with one transaction over the period")
 ; 
#delimit cr 
use ./firm/transaction_all_id_conc$firstyear$lastyear, clear
id_group id_bp "iso2 importer id_conc"
bys id_bp: egen count=count(export)
drop if count==1
id_group id_scp "iso2 siren id_conc"
bys id_scp: egen count_=count(export)
collapse (mean) count_ (count) count_buyer=export (sum) export, by(id_scp siren id_conc iso2 month year)
save temp, replace
collapse (sum) count_buyer export, by(siren id_conc month year)
g Single=(count_buyer==1)
g Multiple=(count_buyer>1)
g More2=(count_buyer>2)
g More5=(count_buyer>5)
g More10=(count_buyer>10)
g More20=(count_buyer>20)
foreach i in Single Multiple More2 More5 More10 More20{
	g Value`i'=export*`i'
}
egen _=sum(Single)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp date") modify
putexcel A2=("All countries") B2=(`sum')
drop _
egen _=sum(ValueSingle)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp date") modify
putexcel I2=(`sum')
drop _
egen _=sum(Multiple)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp date") modify
putexcel C2=(`sum')
drop _
egen _=sum(ValueMultiple)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp date") modify
putexcel J2=(`sum')
drop _
egen _=sum(More2)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp date") modify
putexcel D2=(`sum')
drop _
egen _=sum(ValueMore2)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp*date") modify
putexcel K2=(`sum')
drop _
egen _=sum(More5)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp date") modify
putexcel E2=(`sum')
drop _
egen _=sum(ValueMore5)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp date") modify
putexcel L2=(`sum')
drop _
egen _=sum(More10)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp date") modify
putexcel F2=(`sum')
drop _
egen _=sum(ValueMore10)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp date") modify
putexcel M2=(`sum')
drop _
egen _=sum(More20)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp date") modify
putexcel G2=(`sum')
drop _
egen _=sum(ValueMore20)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp date") modify
putexcel N2=(`sum')
drop _

use temp, clear
g Single=(count_buyer==1)
g Multiple=(count_buyer>1)
g More2=(count_buyer>2)
g More5=(count_buyer>5)
g More10=(count_buyer>10)
g More20=(count_buyer>20)
foreach i in Single Multiple More2 More5 More10 More20{
	g Value`i'=export*`i'
}
local j=1
foreach i in $countrysample{
	local k=2+`j'
	egen _=sum(Single)  if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp date") modify
	putexcel A`k'=("`i'") B`k'=(`sum')
	drop _
	egen _=sum(ValueSingle)  if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp date") modify
	putexcel I`k'=(`sum')
	drop _
	egen _=sum(Multiple)  if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp date") modify
	putexcel C`k'=(`sum')
	drop _
	egen _=sum(ValueMultiple)  if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp date") modify
	putexcel J`k'=(`sum')
	drop _
	egen _=sum(More2)  if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp date") modify
	putexcel D`k'=(`sum')
	drop _
	egen _=sum(ValueMore2)  if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp date") modify
	putexcel K`k'=(`sum')
	drop _	
	egen _=sum(More5)  if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp date") modify
	putexcel E`k'=(`sum')
	drop _
	egen _=sum(ValueMore5)  if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp date") modify
	putexcel L`k'=(`sum')
	drop _	
	egen _=sum(More10)  if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp date") modify
	putexcel F`k'=(`sum')
	drop _
	egen _=sum(ValueMore10)  if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp date") modify
	putexcel M`k'=(`sum')
	drop _	
	egen _=sum(More20)  if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp date") modify
	putexcel G`k'=(`sum')
	drop _
	
	egen _=sum(ValueMore20)  if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp date") modify
	putexcel N`k'=(`sum')
	drop _
	local j=`j'+1
}


*** Statistics on the number of partners per siren
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp") modify
#delimit ; 
putexcel A2=("All countries") 
B1=("Count One") C1=("Count Multiple") D1=("More than 2")
 E1=("More than 5") F1=("More than 10") G1=("More than 20")
 I1=("Count One") J1=("Count Multiple") K1=("More than 2")
 L1=("More than 5") M1=("More than 10") N1=("More than 20")
 A36=("Note: Excludes buyers with one transaction over the period")
 ; 
#delimit cr 
use ./firm/transaction_all_id_conc$firstyear$lastyear, clear
id_group id_bp "iso2 importer id_conc"
bys id_bp: egen count=count(export)
drop if count==1
id_group id_scp "iso2 siren id_conc"
collapse (sum) export, by(id_scp iso2 importer siren id_conc)
collapse (sum) export (count) count_buyer=export, by(id_scp iso2 siren id_conc)
save temp, replace
collapse (sum) count_buyer export, by(siren id_conc)
g Single=(count_buyer==1)
g Multiple=(count_buyer>1)
g More2=(count_buyer>2)
g More5=(count_buyer>5)
g More10=(count_buyer>10)
g More20=(count_buyer>20)
foreach i in Single Multiple More2 More5 More10 More20{
	g Value`i'=export*`i'
}
egen _=sum(Single)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp") modify
putexcel A2=("All countries") B2=(`sum')
drop _
egen _=sum(ValueSingle)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp") modify
putexcel I2=(`sum')
drop _
egen _=sum(Multiple)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp") modify
putexcel C2=(`sum')
drop _
egen _=sum(ValueMultiple)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp") modify
putexcel J2=(`sum')
drop _
egen _=sum(More2)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp") modify
putexcel D2=(`sum')
drop _
egen _=sum(ValueMore2)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp") modify
putexcel K2=(`sum')
drop _
egen _=sum(More5)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp") modify
putexcel E2=(`sum')
drop _
egen _=sum(ValueMore5)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp") modify
putexcel L2=(`sum')
drop _
egen _=sum(More10)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp") modify
putexcel F2=(`sum')
drop _
egen _=sum(ValueMore10)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp") modify
putexcel M2=(`sum')
drop _
egen _=sum(More20)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp") modify
putexcel G2=(`sum')
drop _
egen _=sum(ValueMore20)
sum _
local sum=r(mean)
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp") modify
putexcel N2=(`sum')
drop _

use temp, clear
g Single=(count_buyer==1)
g Multiple=(count_buyer>1)
g More2=(count_buyer>2)
g More5=(count_buyer>5)
g More10=(count_buyer>10)
g More20=(count_buyer>20)
foreach i in Single Multiple More2 More5 More10 More20{
	g Value`i'=export*`i'
}
local j=1
foreach i in $countrysample{
	local k=2+`j'

	egen _=sum(Single)  if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp") modify
	putexcel A`k'=("`i'") B`k'=(`sum')
	drop _
	egen _=sum(ValueSingle)  if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp") modify
	putexcel I`k'=(`sum')
	drop _
	egen _=sum(Multiple)  if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp") modify
	putexcel C`k'=(`sum')
	drop _
	egen _=sum(ValueMultiple)  if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp") modify
	putexcel J`k'=(`sum')
	drop _
	egen _=sum(More2)  if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp") modify
	putexcel D`k'=(`sum')
	drop _
	egen _=sum(ValueMore2)  if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp") modify
	putexcel K`k'=(`sum')
	drop _	
	egen _=sum(More5)  if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp") modify
	putexcel E`k'=(`sum')
	drop _
	egen _=sum(ValueMore5)  if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp") modify
	putexcel L`k'=(`sum')
	drop _	
	egen _=sum(More10)  if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp") modify
	putexcel F`k'=(`sum')
	drop _
	egen _=sum(ValueMore10)  if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp") modify
	putexcel M`k'=(`sum')
	drop _	
	egen _=sum(More20)  if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp") modify
	putexcel G`k'=(`sum')
	drop _
	
	egen _=sum(ValueMore20)  if iso2=="`i'"
	sum _
	local sum=r(mean)
	putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners sp") modify
	putexcel N`k'=(`sum')
	drop _
	local j=`j'+1
}


*** Statistics on the likelihood of purchasing two products from the same siren
putexcel set $outputpath/SummaryStatistics_$firstyear$lastyear , sheet("Count Partners per b across p") modify
#delimit ; 
putexcel A2=("All countries") 
B1=("Count One") C1=("Count Multiple") C1=("Count Multiple")
 A36=("Note: Includes buyers with one transaction over the period")
 ; 
#delimit cr 
use ./firm/transaction_all_id_conc$firstyear$lastyear, clear
rename buyer importer
*id_group id_b "iso2 importer"
id_group id_bp "iso2 importer id_conc"
bys id_bp: egen count=count(export)
*drop if count==1
collapse (mean) count (sum) export, by(iso2 importer id_conc siren)
id_group id_bp "iso2 importer id_conc"
id_group id_b "iso2 importer"
id_group id_bs "iso2 importer siren"
bys id_b: egen count_s=nvals(id_bs)
bys id_b: egen count_p=nvals(id_conc)
bys id_bs: egen count_p_bs=nvals(id_conc)
g oneproduct=(count_p_bs==1)
bys id_bs: g relationship=1 if _n==1

collapse (sum) relationship oneproduct, by(iso2)
g insample=0
foreach i in $countrysample{
	replace insample=1 if iso2=="`i'"
}
keep if insample==1
drop insample
export excel using "$outputpath\SummaryStatistics_19962006.xlsx", sheet("MultipleProducts") firstrow(variables)

use ./firm/transaction_all_id_conc$firstyear$lastyear, clear
rename buyer importer
*id_group id_b "iso2 importer"
id_group id_bp "iso2 importer id_conc"
bys id_bp: egen count=count(export)
*drop if count==1
collapse (mean) count (sum) export, by(iso2 importer id_conc siren year)
id_group id_bsy "iso2 importer siren year"
bys id_bsy: egen count_p_bsy=nvals(id_conc)
g oneproduct=(count_p_bsy==1)
bys id_bsy: g relationship=1 if _n==1
collapse (sum) relationship oneproduct, by(iso2)
g insample=0
foreach i in $countrysample{
	replace insample=1 if iso2=="`i'"
}
keep if insample==1
drop insample

export excel using "$outputpath\SummaryStatistics_19962006.xlsx", sheet("MultipleProducts") sheetmodify cell(A15) firstrow(variables)

use ./firm/transaction_all_id_conc$firstyear$lastyear, clear
rename buyer importer
*id_group id_b "iso2 importer"
id_group id_bp "iso2 importer id_conc"
bys id_bp: egen count=count(export)
*drop if count==1
collapse (mean) count (sum) export, by(iso2 importer id_conc siren year month)
id_group id_bsy "iso2 importer siren year month"
bys id_bsy: egen count_p_bsy=nvals(id_conc)
g oneproduct=(count_p_bsy==1)
bys id_bsy: g relationship=1 if _n==1
collapse (sum) relationship oneproduct, by(iso2)
g insample=0
foreach i in $countrysample{
	replace insample=1 if iso2=="`i'"
}
keep if insample==1
drop insample

export excel using "$outputpath\SummaryStatistics_19962006.xlsx", sheet("MultipleProducts") sheetmodify cell(A29) firstrow(variables)


erase temp.dta
erase temp2.dta
erase ./firm/x_all$firstyear$lastyear.dta